package com.c2soft.nepalcmx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.c2soft.nepalcmx.command.AccountDetailCommand;
import com.c2soft.nepalcmx.command.NetPositionCommand;
import com.c2soft.nepalcmx.command.OrderCommand;
import com.c2soft.nepalcmx.command.ProfitLossCommand;
public class OrderMgmtDAO {

	@Autowired
	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;
	
	public void saveBuyService(OrderCommand buy) {
		System.out.println("Buy Order Mgmt DAO");
		String sql = "insert into ordermgmt (type,otype,insname, symbol,expdate, qt,ql,qw,price,ptype,status,tp,USER_ID) "
				+ "VALUES ('BUY','"+buy.getOtype()+"','"+buy.getInsname()+"','"+buy.getSymbol()+"','"+buy.getExpdate()+"','"+
				buy.getQt()+"','"+buy.getQl()+"','"+buy.getQw()+"','"+buy.getPrice()+"','"+buy.getPtype()
				+"','"+buy.getStatus()+"','"+buy.getTp()+"','"+buy.getUserId()+"')";
		System.out.println("Insertion Query For Buy:"+sql);
		String sql_acct="";
		int cashl=Integer.parseInt(buy.getCashl());
		
		int price=Integer.parseInt(buy.getPrice());
		int lot=Integer.parseInt(buy.getQl());
		int balanced_m=Integer.parseInt(buy.getMargin_b());
		System.out.println("A");
		jdbcTemplate = new JdbcTemplate(dataSource);
		
		String findUser="select Broker_ID from brokers where user_id='"+buy.getUserId()+"' ";
		String broker=jdbcTemplate.queryForObject(findUser,String.class);
		System.out.println("Broker Name for User: "+buy.getUserId()+"is :"+broker);
		System.out.println("B");
		jdbcTemplate.update(sql);
		/*jdbcTemplate.update(sql,
				new Object[] { "Buy",buy.getOtype(),buy.getInsname(), buy.getSymbol(),buy.getExpdate(),
						buy.getQt(), buy.getQl(), buy.getQw(),
						buy.getPrice(),buy.getPtype(), buy.getStatus(),buy.getTp(), buy.getUserId() });
		*/
		System.out.println("C");
		
		if(buy.getPtype().equals("CarryForward")){
			System.out.println("Query For Carry Forward");
			sql_acct="insert into account(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,DEBITAMT,remark) values(?,?,?,?,?,?,?,'"+ buy.getQl()+" lots of "+buy.getSymbol()+" buyed.') ";
			int newCashl=cashl-(price*lot);
			
			jdbcTemplate.update(sql_acct,
					new Object[] {broker,buy.getUserId(),buy.getAccno(),newCashl,newCashl+100000,newCashl+100000,price*lot});
			System.out.println("End Query For Carry Forward");
		}
		else if(buy.getPtype().equals("Intra")){
			System.out.println("Query For Intra");
			sql_acct="insert into account(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,UTILISED_MARGIN,remark) values(?,?,?,?,?,?,?,'"+ buy.getQl()+" lots of "+buy.getSymbol()+" buyed.')";
			jdbcTemplate.update(sql_acct,
					new Object[] {broker,buy.getUserId(),buy.getAccno(),buy.getCashl(),/*buy.getMarginl()*/balanced_m,balanced_m-(price*lot),price*lot});
			System.out.println("End Query For Carry Forward");
		}
	}

	
	
	public void saveSellService(OrderCommand sell) {
		int cashl=Integer.parseInt(sell.getCashl());
		int balanced_m=Integer.parseInt(sell.getMargin_b());
		int price=Integer.parseInt(sell.getPrice());
		int lot=Integer.parseInt(sell.getQl());
		String sql = "insert into ordermgmt (type,otype,insname, symbol,expdate, qt,ql,qw,price,ptype,status,tp,USER_ID) VALUES (?,?,?,?,?, ?,?, ?,?,?,?,?,?)";
		String sql_acct="";
		jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql,
				new Object[] { "SELL",sell.getOtype(),sell.getInsname(), sell.getSymbol(),sell.getExpdate(),
				sell.getQt(), sell.getQl(), sell.getQw(),
				sell.getPrice(),sell.getPtype(),sell.getStatus(), sell.getTp(), sell.getUserId() });
		
		
		String findUser="select Broker_ID from brokers where user_id='"+sell.getUserId()+"' ";
		String broker=jdbcTemplate.queryForObject(findUser,String.class);
	
		
	if(sell.getPtype().equals("CarryForward")){
		
		sql_acct="INSERT INTO ACCOUNT(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,CREDITAMT,remark) values(?,?,?,?,?,?,?,'"+ sell.getQl()+" lots of "+sell.getSymbol()+" sold.')";
		int newCashl=cashl+(price*lot);
		jdbcTemplate.update(sql_acct,
				new Object[] {broker,sell.getUserId(),sell.getAccno(),newCashl,newCashl+100000,newCashl+100000,price*lot});
	}
	else if(sell.getPtype().equals("Intra")){
	
		sql_acct="insert into account(BROKER_ID,USER_ID,ACCNO,CASHL,MARGINL,BALANCED_MARGIN,remark) values(?,?,?,?,?,?,'"+ sell.getQl()+" lots of "+sell.getSymbol()+" sold.')";
		jdbcTemplate.update(sql_acct,
				new Object[] {broker,sell.getUserId(),sell.getAccno(),sell.getCashl(),balanced_m+(price*lot),balanced_m+(price*lot)});
		
	}
	}
	
	
	
	public List<NetPositionCommand> findBuyOrder(String userId) {
		String sql = "SELECT sum(ql),SYMBOL,ptype,user_Id from ordermgmt where type='BUY' and user_id=? and type='BUY' group by symbol,ptype  ";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<NetPositionCommand> buyOrder=jdbcTemplate.query(sql,new Object[]{userId},new BuyRowMapper());
		return buyOrder;

	
}
	private final class BuyRowMapper implements RowMapper<NetPositionCommand> {

	    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	NetPositionCommand netpos = new NetPositionCommand();
	    	netpos.setLot1(rs.getString(1));
	    	netpos.setSymbol(rs.getString(2));
	    	netpos.setPtype(rs.getString(3));
	    	netpos.setUserId(rs.getString(4));
	    	String sql1 ="SELECT SUM(ql),symbol FROM ordermgmt o where type='SELL' and user_Id='"+
	    	netpos.getUserId()+"' and ptype='"+netpos.getPtype()+"' group by symbol";
	    	List lots=jdbcTemplate.query(sql1,new LotMapper());
	    	
	    	Iterator itr=lots.iterator();
	    	while(itr.hasNext()){
	    		NetPositionCommand npc=(NetPositionCommand)itr.next();
	    		if(npc.getSymbol().equals(netpos.getSymbol())){
	    		netpos.setLot2(npc.getLot2());
	    		}
	    	}
	        return netpos;
	    } 
	}
	private final class LotMapper implements RowMapper<NetPositionCommand> {

	    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	NetPositionCommand netpos = new NetPositionCommand();
	    	netpos.setLot2(rs.getString(1));
	    	netpos.setSymbol(rs.getString(2));
	    	
	        return netpos;
	    } 
	}
}